NTSB Flight Database examples

The follow examples all run against the model at the bottom of this page OR you can find the source code here.

Airport Dashboard

Where can you fly from SJC? For each destination; Which carriers? How long have they been flying there? Are they ontime?

explore flights : [origin.code : 'SJC']
| airport_dashboard

destination_code

LAX

destination

LOS ANGELES INTL

flight_count

475

carriers_by_month

routes_map

delay_by_hour_of_day

destination_code

SEA

destination

SEATTLE-TACOMA INTL

flight_count

368

carriers_by_month

routes_map

delay_by_hour_of_day

destination_code

SAN

destination

SAN DIEGO INTL-LINDBERGH FLD

flight_count

363

carriers_by_month

routes_map

delay_by_hour_of_day

destination_code

PHX

destination

PHOENIX SKY HARBOR INTL

flight_count

314

carriers_by_month

routes_map

delay_by_hour_of_day

destination_code

LAS

destination

MC CARRAN INTL

flight_count

303

carriers_by_month

routes_map

delay_by_hour_of_day

destination_code

BUR

destination

BURBANK-GLENDALE-PASADENA

flight_count

240

carriers_by_month

routes_map

delay_by_hour_of_day

destination_code

ORD

destination

CHICAGO O'HARE INTL

flight_count

233

carriers_by_month

routes_map

delay_by_hour_of_day

destination_code

ONT

destination

ONTARIO INTL

flight_count

232

carriers_by_month

routes_map

delay_by_hour_of_day

destination_code

PDX

destination

PORTLAND INTL

flight_count

223

carriers_by_month

routes_map

delay_by_hour_of_day

destination_code

RNO

destination

RENO/TAHOE INTERNATIONAL

flight_count

196

carriers_by_month

routes_map

delay_by_hour_of_day

Tell me everything about a carrier. How many destinations?, flights? hubs? What kind of planes to they use? How many flights over time? What are the major hubs? For each destionation, How many flights? Where can you? Have they been flying there long? Increasing or decresing year by year? Any seasonality?

explore flights : [carriers.nickname : 'Jetblue']
| carrier_dashboard

destination_count

flight_count

4,842

by_manufacturer

by_month

hubs

hub	destination_count
JFK - JOHN F KENNEDY INTL	26
BOS - GENERAL EDWARD LAWRENCE LOGAN INTL	9
LGB - LONG BEACH /DAUGHERTY FIELD/	8
EWR - NEWARK INTL	6
FLL - FORT LAUDERDALE/HOLLYWOOD INTL	6
IAD - WASHINGTON DULLES INTERNATIONAL	5
OAK - METROPOLITAN OAKLAND INTL	5
PBI - PALM BEACH INTL	3
RSW - SOUTHWEST FLORIDA INTL	3
LAS - MC CARRAN INTL	3

origin_code

JFK

origin

JOHN F KENNEDY INTL

city

NEW YORK

flight_count

1,752

destinations_by_month

routes_map

year_over_year

origin_code

LGB

origin

LONG BEACH /DAUGHERTY FIELD/

city

LONG BEACH

flight_count

471

destinations_by_month

routes_map

year_over_year

origin_code

FLL

origin

FORT LAUDERDALE/HOLLYWOOD INTL

city

FORT LAUDERDALE

flight_count

375

destinations_by_month

routes_map

year_over_year

origin_code

OAK

origin

METROPOLITAN OAKLAND INTL

city

OAKLAND

flight_count

297

destinations_by_month

routes_map

year_over_year

origin_code

MCO

origin

ORLANDO INTL

city

ORLANDO

flight_count

245

destinations_by_month

routes_map

year_over_year

origin_code

IAD

origin

WASHINGTON DULLES INTERNATIONAL

city

WASHINGTON

flight_count

205

destinations_by_month

routes_map

year_over_year

origin_code

BOS

origin

GENERAL EDWARD LAWRENCE LOGAN INTL

city

BOSTON

flight_count

185

destinations_by_month

routes_map

year_over_year

origin_code

LAS

origin

MC CARRAN INTL

city

LAS VEGAS

flight_count

146

destinations_by_month

routes_map

year_over_year

origin_code

PBI

origin

PALM BEACH INTL

city

WEST PALM BEACH

flight_count

143

destinations_by_month

routes_map

year_over_year

origin_code

TPA

origin

TAMPA INTL

city

TAMPA

flight_count

141

destinations_by_month

routes_map

year_over_year

Kayak Example Query

Suppose you wanted to build a website like Kayak. Let's assume that the data we have is in the future instead ofthe past. The query below will fetch all the data needed to render a Kayak page in a singe query.

explore flights : [
  origin.code : 'SJC',
  destination.code : 'LAX'|'BUR',
  dep_time : @2004-01-01
]
| kayak

carriers

by_hour

flights

nickname	flight_count

dep_hour	flight_count

dep_minute	name	flight_num	origin_code	destination_code	manufacturer	model

Sessionizing Flight Data.

You can think of flight data as event data. The below is a classic map/reduce roll up of the filght data by carrier and day, plane and day, and individual events for each plane.

sessionize is ( reduce
  flight_date is dep_time.`date`
  carrier
  daily_flight_count is flight_count
  per_plane_data is (reduce top 20
    tail_num
    plane_flight_count is flight_count
    flight_legs is (reduce order by 2
      tail_num
      dep_minute is dep_time.minute
      origin_code
      dest_code is destination_code
      dep_delay
      arr_delay
    )
  )
)

explore flights : [carrier:'WN', dep_time: @2002-03-03]
| sessionize

dep_time_date

carrier

daily_flight_count

per_plane_data

2002-03-03

tail_num

plane_flight_count

flight_legs

N735SA

tail_num	dep_minute	origin_code	destination_code	dep_delay	arr_delay
N735SA	2002-03-03 07:15	OAK	LAS	0	-10
N735SA	2002-03-03 09:00	LAS	SMF	0	5
N735SA	2002-03-03 11:00	SMF	SAN	15	10
N735SA	2002-03-03 12:45	SAN	LAS	20	17
N735SA	2002-03-03 14:14	LAS	SLC	19	19
N735SA	2002-03-03 16:55	SLC	PHX	25	10
N735SA	2002-03-03 18:53	PHX	SLC	18	10
N735SA	2002-03-03 20:45	SLC	RNO	15	10
N735SA	2002-03-03 21:27	RNO	PDX	12	5

N641SW

tail_num	dep_minute	origin_code	destination_code	dep_delay	arr_delay
N641SW	2002-03-03 09:15	OAK	ONT	0	-1
N641SW	2002-03-03 10:50	ONT	OAK	0	0
N641SW	2002-03-03 12:35	OAK	PDX	10	5
N641SW	2002-03-03 14:25	PDX	OAK	0	-3
N641SW	2002-03-03 16:37	OAK	LAX	7	0
N641SW	2002-03-03 18:15	LAX	ELP	0	7
N641SW	2002-03-03 21:30	ELP	MAF	0	0

N628SW

tail_num	dep_minute	origin_code	destination_code	dep_delay	arr_delay
N628SW	2002-03-03 08:00	OAK	LAX	0	-7
N628SW	2002-03-03 10:00	LAX	OAK	0	5
N628SW	2002-03-03 11:45	OAK	GEG	10	0
N628SW	2002-03-03 13:55	GEG	SEA	0	-5
N628SW	2002-03-03 15:25	SEA	RNO	0	-12
N628SW	2002-03-03 17:30	RNO	LAS	0	-10
N628SW	2002-03-03 19:23	LAS	PHX	13	20

N391SW

tail_num	dep_minute	origin_code	destination_code	dep_delay	arr_delay
N391SW	2002-03-03 07:50	BWI	PVD	0	0
N391SW	2002-03-03 09:44	PVD	TPA	14	19
N391SW	2002-03-03 13:50	TPA	PVD	35	48
N391SW	2002-03-03 17:08	PVD	BNA	38	61
N391SW	2002-03-03 19:40	BNA	HOU	50	60

N791SW

tail_num	dep_minute	origin_code	destination_code	dep_delay	arr_delay
N791SW	2002-03-03 08:00	TPA	LAS	0	12
N791SW	2002-03-03 10:37	LAS	MDW	17	22
N791SW	2002-03-03 16:45	MDW	STL	25	25
N791SW	2002-03-03 18:15	STL	MCO	20	10
N791SW	2002-03-03 22:05	MCO	FLL	20	20

N723SW

tail_num	dep_minute	origin_code	destination_code	dep_delay	arr_delay
N723SW	2002-03-03 08:40	PHX	PVD	0	-16
N723SW	2002-03-03 15:55	PVD	PHX	0	-30
N723SW	2002-03-03 20:20	PHX	SAN	5	-12
N723SW	2002-03-03 21:00	SAN	PHX	0	0

The Malloy Model

All of the queries above are executed against the following model:

export define airports is (explore 'malloy-data.faa.airports'
  primary key code
  name is concat(code, ' - ', full_name)
  airport_count is count()
);

define carriers is (explore 'malloy-data.faa.carriers'
  primary key code
  carrier_count is count()
);

define aircraft_models is (explore 'malloy-data.faa.aircraft_models'
  primary key aircraft_model_code
  aircraft_model_count is count()
);

define aircraft is (explore 'malloy-data.faa.aircraft'
  primary key tail_num
  aircraft_count is count()
  -- joins
  aircraft_models is join on aircraft_model_code
);

export define flights_base is (explore 'malloy-data.faa.flights'
  primary key id2
);

define aircraft_facts is (explore
  (explore flights_base | reduce
    tail_num
    lifetime_flights is count()
    lifetime_distance is distance.sum()
  )
  lifetime_flights_bucketed is floor(lifetime_flights/1000)*1000
);

export define flights is (explore flights_base
  primary key id2
  -- rename some fields
  origin_code renames origin
  destination_code renames destination

  -- joins
  carriers is join on carrier
  origin is join airports on origin_code
  destination is join airports on destination_code,
  aircraft is join on tail_num
  aircraft_facts is join on tail_num

  -- measures
  flight_count is count()
  total_distance is sum(distance)
  seats_for_sale is sum(aircraft.aircraft_models.seats)
  seats_owned is aircraft.sum(aircraft.aircraft_models.seats)

  -- queries
  measures is (reduce
    flight_count
    aircraft.aircraft_count
    dest_count is destination.airport_count
    origin_count is origin.airport_count
  )

  -- shows carriers and number of destinations (bar chart)
  by_carrier is (reduce
    carriers.nickname
    flight_count
    destination_count is destination.count()
  )

  -- shows year over year growth (line chart)
  year_over_year is (reduce
    dep_month is month(dep_time)
    flight_count
    dep_year is dep_time.year
  )

  -- shows plane manufacturers and frequency of use
  by_manufacturer is (reduce top 5
    aircraft.aircraft_models.manufacturer
    aircraft.aircraft_count
    flight_count
  )

  delay_by_hour_of_day is (reduce : [dep_delay >30]
    dep_hour is hour(dep_time)
    flight_count
    delay is FLOOR(dep_delay)/30 * 30
  )

  carriers_by_month is (reduce
    dep_month is dep_time.month
    flight_count
    carriers.nickname
  )

  seats_by_distance is (reduce
    -- seats rounded to 5
    seats is floor(aircraft.aircraft_models.seats/5)*5
    flight_count
    -- distance rounded to 20
    distance is floor(distance/20)*20
  )

  routes_map is (reduce
    origin.latitude
    origin.longitude
    latitude2 is destination.latitude
    longitude2 is destination.longitude
    flight_count
  )

  destinations_by_month is (reduce
    dep_time.`month`
    flight_count
    destination.name
  )

  -- explore flights : [origin.code : 'SJC'] | airport_dashboard
  airport_dashboard is ( reduce top 10
    code is destination_code
    destination is destination.full_name
    flight_count
    carriers_by_month
    routes_map
    delay_by_hour_of_day
  )

  plane_usage is (reduce order by 1 desc : [aircraft.aircraft_count > 1]
    aircraft_facts.lifetime_flights_bucketed
    aircraft.aircraft_count
    flight_count
    by_manufacturer
    by_carrier
  )

  -- explore flights : [carriers.nickname : 'Southwest'] | carrier_dashboard
  carrier_dashboard is ( reduce
    destination_count is destination.airport_count
    flight_count
    by_manufacturer
    by_month is (reduce
      dep_month is dep_time.month
      flight_count
    )
    hubs is (reduce : [destination.airport_count > 1] top 10
      hub is origin.name
      destination_count is destination.airport_count
    )
    origin_dashboard is (reduce top 10
      code is origin_code
      origin is origin.full_name
      origin.city
      flight_count
      destinations_by_month
      routes_map
      year_over_year
    )
  )

  detail is (project top 30 order by 2
    id2, dep_time, tail_num, carrier, origin_code, destination_code, distance
    aircraft.aircraft_model_code
  )

  -- query that you might run for to build a flight search interface
  --   explore flights : [origin.code: 'SJC', destination.code:'LAX'|'BUR', dep_time: @2004-01-01] | kayak
  kayak is (reduce
    carriers is (reduce
      carriers.nickname
      flight_count
    )
    by_hour is (reduce order by 1
      dep_hour is hour(dep_time)
      flight_count
    )
    flights is (reduce
      dep_minute is dep_time.minute
      carriers.name
      flight_num
      origin_code
      destination_code
      aircraft.aircraft_models.manufacturer
      aircraft.aircraft_models.model
    )
  )

  -- example query that shows how you can build a map reduce job to sessionize flights
  sessionize is (reduce
    flight_date is dep_time.`date`
    carrier
    daily_flight_count is flight_count
    per_plane_data is (reduce top 20
      tail_num
      plane_flight_count is flight_count
      flight_legs is (reduce order by 2
        tail_num
        dep_minute is dep_time.minute
        origin_code
        dest_code is destination_code
        dep_delay
        arr_delay
      )
    )
  )

  search_index is (index : [dep_time: @2004-01]
    *, carriers.*,
    origin.code, origin.state, origin.city, origin.full_name, origin.fac_type
    destination.code, destination.state, destination.city, destination.full_name
    aircraft.aircraft_model_code, aircraft.aircraft_models.manufacturer
    aircraft.aircraft_models.model
    on flight_count
  )
);

Data Styles

The data styles tell the Malloy renderer how to render different kinds of results.

{
  "by_carrier": {
    "renderer": "bar_chart"
  },
  "year_over_year": {
    "renderer": "line_chart"
  },
  "by_month": {
    "renderer": "line_chart"
  },
  "by_manufacturer": {
    "renderer": "bar_chart"
  },
  "routes_map": {
    "renderer": "segment_map"
  },
  "destinations_by_month": {
    "renderer": "line_chart"
  },
  "delay_by_hour_of_day": {
    "renderer" : "scatter_chart"
  },
  "seats_by_distance": {
    "renderer": "scatter_chart"
  },
  "carriers_by_month" : {
    "renderer": "line_chart"
  }
}

NTSB Flight Database examples

Airport Dashboard

Carrier Dashboard

Kayak Example Query

Sessionizing Flight Data.

The Malloy Model

Data Styles